﻿// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MsSqlSpatial;

public partial class TableValuedFunctions
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="table_name_1"></param>
    /// <param name="column_name_1"></param>
    /// <param name="table_name_2"></param>
    /// <param name="column_name_2"></param>
    /// <returns>TABLE(oid1 bigint, oid2 bigint)</returns>
    /// <example>
    /// "What is the length of roads fully contained within each municipality?"
    /// 
    ///		SELECT m.name, sum(ST.Length(r.the_geom))/1000 AS roads_km
    ///		FROM ST.JoinFilterQuery('bc_roads','the_geom','bc_municipality','the_geom') AS q
    ///		INNER JOIN bc_roads AS r ON q.oid1 = r.oid
    ///		INNER JOIN bc_municipality AS m ON q.oid2 = m.oid
    ///		WHERE ST.[Contains](m.the_geom,r.the_geom)=1
    ///		GROUP BY m.name
    ///		ORDER BY roads_km
    /// 
    /// </example>
    [SqlFunction(
        FillRowMethodName = "FillRowJoinFilterQuery"
        , TableDefinition = "oid1 bigint, oid2 bigint"
        , DataAccess = DataAccessKind.Read
        , SystemDataAccess = SystemDataAccessKind.Read
        )]
    public static IEnumerable JoinFilterQuery(
        SqlString table_name_1,
        SqlString column_name_1,
        SqlString table_name_2,
        SqlString column_name_2)
    {
        var oids = new List<Int64[]>();

        using (SqlConnection connection = DatabaseHelper.ContextConnection)
        {
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                string tableName1 = table_name_1.Value;
                string columnName1 = column_name_1.Value;
                string tableName2 = table_name_2.Value;
                string columnName2 = column_name_2.Value;

                string schemaName1 = "";
                string schemaName2 = "";

                DatabaseHelper.SplitSchemaAndTableName(command, ref tableName1, ref schemaName1);
                DatabaseHelper.SplitSchemaAndTableName(command, ref tableName2, ref schemaName2);

                int count1 = CountTableRows(command, tableName1, schemaName1);
                int count2 = CountTableRows(command, tableName2, schemaName2);

                const string tableAlias1 = "gis_t1";
                const string tableAlias2 = "gis_t2";

                string swappedTableAlias1;
                string swappedTableAlias2;
                string swappedColumnName1;
                string swappedColumnName2;

                // Use indexes of the table with more rows.
                if (count1 > count2)
                {
                    swappedTableAlias1 = tableAlias1;
                    swappedTableAlias2 = tableAlias2;
                    swappedColumnName1 = columnName1;
                    swappedColumnName2 = columnName2;
                }
                else
                {
                    swappedTableAlias1 = tableAlias2;
                    swappedTableAlias2 = tableAlias1;
                    swappedColumnName1 = columnName2;
                    swappedColumnName2 = columnName1;
                }

                string primaryKey1 = GeometryTable.GetPrimaryKeyColumn(command, schemaName1, tableName1);
                string primaryKey2 = GeometryTable.GetPrimaryKeyColumn(command, schemaName2, tableName2);

                DatabaseHelper.BuildCommand(command, string.Format(
                                                         @"SELECT [{0}].[{10}], [{1}].[{11}] 
FROM [{2}].[{3}] as [{0}], [{4}].[{5}] as [{1}]
WHERE (
[{6}].[{7}_Envelope_MinX] < ST.MaxX([{8}].[{9}]) AND
[{6}].[{7}_Envelope_MaxX] > ST.MinX([{8}].[{9}]) AND
[{6}].[{7}_Envelope_MinY] < ST.MaxY([{8}].[{9}]) AND
[{6}].[{7}_Envelope_MaxY] > ST.MinY([{8}].[{9}])
)"
                                                         , tableAlias1 // {0}
                                                         , tableAlias2 // {1}
                                                         , schemaName1 // {2}
                                                         , tableName1 // {3}
                                                         , schemaName2 // {4}
                                                         , tableName2 // {5}
                                                         , swappedTableAlias1 // {6}
                                                         , swappedColumnName1 // {7}
                                                         , swappedTableAlias2 // {8}
                                                         , swappedColumnName2 // {9}
                                                         , primaryKey1 // {10}
                                                         , primaryKey2 // {11}
                                                         ));

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        oids.Add(new[] {(long) reader.GetValue(0), (long) reader.GetValue(1)});
                    }
                }
            }
        }
        return oids;
    }

    private static int CountTableRows(SqlCommand command, string tableName, string schemaName)
    {
        DatabaseHelper.BuildCommand(command, string.Format(
                                                 @"SELECT count(*)
FROM [{0}].[{1}]"
                                                 , schemaName, tableName));
        return (int) command.ExecuteScalar();
    }

    public static void FillRowJoinFilterQuery(Object obj, out SqlInt64 oid1, out SqlInt64 oid2)
    {
        oid1 = new SqlInt64(((Int64[]) obj)[0]);
        oid2 = new SqlInt64(((Int64[]) obj)[1]);
    }
} ;